package Homework.cms.articles;

import Homework.cms.util.JDBCHelper;
import Homework.cms.util.Page;
import Homework.cms.util.ResultFs;

import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class ArticlesDao {
    public static boolean insert(Articles articles){
        String sql="INSERT INTO articles(writter,title,content,addtime,updatetime) VALUES (?,?,?,?,?)";
        Timestamp nowtime = new Timestamp((new java.util.Date()).getTime());
        int cout= JDBCHelper.update(sql,articles.getWritter(),articles.getTitle(),articles.getContent(),nowtime,nowtime);
        return cout>0;
    }

    public static boolean remove(Articles articles){
        int id=articles.getId();
        String sql="DELETE FROM articles WHERE id="+articles.getId();
        int cout= JDBCHelper.delete(sql);
        String sql2="update articles set id=id-1 where id>="+(id+1);  //对序号重新排序
        int cout2= JDBCHelper.update(sql2);
        String sql3="alter table articles auto_increment = 1";
        int cout3=JDBCHelper.update(sql3);
        return cout2>0 && cout>0 && cout3>0;
    }

    public static boolean update(Articles articles){
        Timestamp nowtime = new Timestamp((new java.util.Date()).getTime());
        //+",updatetime="+nowtime           ,updatetime=?
        String sql="UPDATE articles SET title=?,content=?,updatetime=? WHERE id=?";
        int cout= JDBCHelper.update(sql,articles.getTitle(),articles.getContent(),nowtime,articles.getId());
        return cout>0;
    }

    //为了记录每个作品的网址，选择正序排列，每页标号对应数据库里的id号
    //也可以用来用户修改作品
    public static Page<Articles> getPage(int pageSize, int currPage){
        Page<Articles> page=null;
        int totalCount=0;
        String sql="SELECT count(1) from articles"; //得到数据库中有多少条数据，count(1)比count(*)运行快
        try {
            //1.获取总条数
            ResultSet rs=JDBCHelper.query(sql);
            if(rs.next())   //如果能查到数据
            {
                totalCount=rs.getInt(1);    //得到第一列的数据（数据总条数）
            }
            page=new Page<Articles>(totalCount,pageSize,currPage);
            //2.设置当前页的数据
            sql="SELECT * FROM articles LIMIT "+page.getCurrpageindex()+","+pageSize;  //当前页的索引和每一页的大小
            rs=JDBCHelper.query(sql);
            List<Articles> articles= ResultFs.getList(rs,"Homework.cms.articles.Articles");
            page.setRecords(articles);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        finally {
            JDBCHelper.close();
        }
        return page;
    }

    //为了在前、后台展示所有用户的作品，选择倒叙排列
    public static Page<Articles> allusersarticles(int pageSize, int currPage){
        Page<Articles> page=null;
        int totalCount=0;
        String sql="SELECT count(1) from articles"; //得到数据库中有多少条数据，count(1)比count(*)运行快
        try {
            //1.获取总条数
            ResultSet rs=JDBCHelper.query(sql);
            if(rs.next())   //如果能查到数据
            {
                totalCount=rs.getInt(1);    //得到第一列的数据（数据总条数）
            }
            page=new Page<Articles>(totalCount,pageSize,currPage);
            //2.设置当前页的数据
            sql="SELECT * FROM articles ORDER BY id DESC LIMIT "+page.getCurrpageindex()+","+pageSize;  //当前页的索引和每一页的大小
            rs=JDBCHelper.query(sql);
            List<Articles> articles= ResultFs.getList(rs,"Homework.cms.articles.Articles");
            page.setRecords(articles);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        finally {
            JDBCHelper.close();
        }
        return page;
    }

    //为了在前、后台展示指定用户的作品，选择倒叙排列
    public static Page<Articles> usersarticles(int pageSize, int currPage, Articles articles){
        Page<Articles> page=null;
        int totalCount=0;
        String sql="SELECT count(1) from articles WHERE writter="+articles.getWritter(); //得到数据库中有多少条数据，count(1)比count(*)运行快
        try {
            //1.获取总条数
            ResultSet rs=JDBCHelper.query(sql);
            if(rs.next())   //如果能查到数据
            {
                totalCount=rs.getInt(1);    //得到第一列的数据（数据总条数）
            }
            page=new Page<Articles>(totalCount,pageSize,currPage);
            //2.设置当前页的数据
            sql="SELECT * FROM articles WHERE writter="+articles.getWritter()+" ORDER BY id DESC LIMIT "+page.getCurrpageindex()+","+pageSize;  //当前页的索引和每一页的大小
            rs=JDBCHelper.query(sql);
            List<Articles> userarticles= ResultFs.getList(rs,"Homework.cms.articles.Articles");
            page.setRecords(userarticles);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        finally {
            JDBCHelper.close();
        }
        return page;
    }

    public static List<Articles> getLatestArticles() {
        List<Articles> articles=new ArrayList<>();
        try {
            ResultSet rs= JDBCHelper.query("SELECT * FROM articles ORDER BY id DESC LIMIT 0,10");
            //         while (rs!=null&&rs.next())
//            {
//                News n=new News();
//                n.setTitle(rs.getString("title"));
//                n.setUrl(rs.getString("url"));
//                news.add(n);
//            }
            articles= ResultFs.getList(rs,"Homework.cms.articles.Articles");
        } catch (Exception e) {
            System.out.println("获取作品数据出错"+e.getMessage());
        } finally {
            JDBCHelper.close();
        }

        return articles;
    }
}
